﻿using InDepth2Demo.Common;
using InDepth2Demo.Model;
using System;
using System.Data.Linq;
using System.Linq;

namespace InDepth2Demo.Chapter12
{
    internal class LinqToSql
    {
        public static void Call()
        {
            using (var context = new LinqDemoDataContext())
            {
                Table<Employee> employeesTable = context.GetTable<Employee>();

                context.Log = Console.Out;
                //Employee emp = employeesTable
                //                        .Where(e => e.Name == "王珊珊")
                //                        .FirstOrDefault();

                var query = from emps in employeesTable
                            where emps.Status != 2
                            where emps.Name == "王珊珊"
                            select emps.Code;
                foreach (var code in query)
                {
                    Console.WriteLine(code);
                }
            }
        }

        public static void Call2()
        {
            using (var context = new LinqDemoDataContext())
            {
                context.Log = Console.Out;
                var employeesTable = context.GetTable<Employee>(); // 尝试显式获取表对象
                if (employeesTable == null)
                {
                    Console.WriteLine("Employees table is null.");
                }

                var testsTable = context.GetTable<Z_Test>();
                var query = from test in testsTable
                            where test.Name == "一一"
                            select test;
            }
        }

        //可用
        public static void Call3()
        {
            using (var context = new CHUNKEntities1())
            {
                var query = context.mpUser.First();
                Console.WriteLine(query.empCode + query.nickName);
            }
        }

        // let 子句
        public static void Call4()
        {
            using (var context = new CHUNKEntities1())
            {
                var query = (from user in context.mpUser
                             let length = user.nickName.Length
                             orderby length descending
                             select new { Name = user.nickName, Length = length }).Take(10);
                foreach (var item in query)
                {
                    Console.WriteLine($"{item.Name}, {item.Length}");
                }
            }
            // 生成的sql
            //SELECT[t1].[Name], [t1].[value]
            //  FROM(
            //      SELECT LEN([t0].[Name]) AS[value], [t0].[Name]
            //      FROM [dbo].[DefectUser] As[t0]
            //  ) AS[t1]
            //ORDER BY[t1].[value]

        }
    }
}
